﻿using DBService.DataHelper;
using DBService.Handler.Base;
using Entity;
using Entity.SysEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBService.Handler
{
    public class DbHandler 
    {
        DbHelper db;
        SqlHelper helper;
        TranslationObject trans;
        public DbHandler()
        {
            db = new DbHelper();
            helper = new SqlHelper();
            trans = new TranslationObject();
        }
        /// <summary>
        /// 根据数据模型获取数据集合
        /// </summary>
        /// <param name="obj">数据模型</param>
        /// <returns>table数据表</returns>
        public virtual DataTable Query(object obj)
        {
            var option = trans.TranslationMode(obj);
            return this.DBQuery(option);

        }
        /// <summary>
        /// 数据查询方法
        /// </summary>
        /// <param name="option">对象结构模型</param>
        /// <returns></returns>
        private DataTable DBQuery(QueryOption option)
        {
            StringBuilder sqlString = new StringBuilder();
            var tables = helper.CreateParameterString(option.QueryList, null);
            var list = (List<SqlParameter>)tables["parameter"];
            var whereString = (string)tables["whereString"];
            SqlParameter[] par = list.ToArray();
            string files = "";
            if (option.Fields == null || option.Fields.Trim().Equals(""))
            {
                files = "*";
            }
            else
            {
                files = option.Fields.Trim();
            }
            sqlString.Append("select " + files + " from " + option.TableName);
            sqlString.Append(" where ");
            if (whereString == null || whereString.Trim().Equals(""))
            {
                whereString = "1=1";
            }

            sqlString.Append(whereString);
            return db.ExecuteDataTable(sqlString.ToString(), list);
        }

        #region 数据更新方法
        /// <summary>
        /// 数据更新方法(适用于添加、删除、修改)
        /// </summary>
        /// <param name="list">结果集合集</param>
        /// <returns></returns>
        public virtual ReturnResult TransUpdate(List<OperateModel> list)
        {
            ReturnResult rsl = new ReturnResult();
            int i = 0;
            StringBuilder sql;
            List<string> sqlList = new List<string>();
            List<SqlParameter[]> parlist = new List<SqlParameter[]>();//参数集合
            foreach (OperateModel model in list)
            {
                sql = new StringBuilder();
                var modelAttr = (CustomAttribute)model.Data.GetType().GetCustomAttributes(true)[0];//获取模型对象自定义特性
                if (modelAttr.TableName == null || modelAttr.TableName.Trim().Equals(""))
                {
                    rsl.Message = string.Format("对象模型特性设置不完整");
                }
                if (model.Types == OperateModel.OperateType.Add)
                {
                    #region 添加方法
                    var tables = helper.AddCreate(model.Data, i.ToString());
                    var par = (List<SqlParameter>)tables["parameter"];
                    sql.Append("insert into " + modelAttr.TableName);
                    sql.Append(" (" + tables["columns"] + ")");
                    sql.Append(" values (" + tables["values"] + ")");
                    sqlList.Add(sql.ToString());
                    parlist.Add(par.ToArray());
                    
                    #endregion
                }
                else if (model.Types == OperateModel.OperateType.Edit)
                {
                    #region 修改方法
                    var op = trans.TranslationMode(model.Data);
                    if (string.IsNullOrWhiteSpace(model.Conditions))
                    {
                        rsl.Message = "作为修改语句，请先设置where条件";
                        return rsl;
                    }
                    var tables = helper.EditCreate(op.QueryList, model.Conditions, i.ToString());
                    var par = (List<SqlParameter>)tables["parameter"];
                    sql.Append("update " + modelAttr.TableName);
                    sql.Append(" set " + tables["set"]);
                    sql.Append(" where " + tables["where"]);
                    sqlList.Add(sql.ToString());
                    parlist.Add(par.ToArray());
                    #endregion
                }
                else if (model.Types == OperateModel.OperateType.Delete)
                {
                    #region 删除方法
                    var option = trans.TranslationMode(model.Data);
                    var tables = helper.CreateParameterString(option.QueryList, i.ToString());
                    var whereString = (string)tables["whereString"];
                    var par = (List<SqlParameter>)tables["parameter"];
                    sql.Append("delete from " + modelAttr.TableName);
                    sql.Append(" where " + whereString);
                    sqlList.Add(sql.ToString());
                    parlist.Add(par.ToArray());
                    #endregion
                }
                i++;
            }
            rsl = db.TransUpdate(sqlList, parlist);
            return rsl;
        }
        #endregion

        public virtual ReturnResult TransUpdate(List<string> list)
        {
            return db.TransUpdate(list, null);
        }
        /// <summary>
        /// sql更新数据
        /// </summary>
        /// <param name="sql">执行sql</param>
        /// <returns></returns>
        public ReturnResult UpdataBySql(string sql)
        {
           return  db.ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 查询语句
        /// </summary>
        /// <param name="sql">执行sql</param>
        /// <returns></returns>
        public DataTable QueryBySql(string sql)
        {
            return db.ExecuteDataTable(sql, null);
        }
        #region 数据分页
       /// <summary>
       /// 数据分页查询方法
       /// </summary>
       /// <param name="tableName">表名称</param>
       /// <param name="pageIndex">页码</param>
       /// <param name="pageSize">页面信息数</param>
       /// <param name="sqlWhere">检索条件</param>
       /// <param name="orderStr">排序</param>
       /// <param name="pageCount">页数</param>
       /// <param name="fileds">查询字段</param>
       /// <returns></returns>
        public DataTable PageList(string tableName, string pageIndex, string pageSize, string sqlWhere, string orderStr, ref string pageCount,string fileds)
        {
            SqlParameter[] parameters = new SqlParameter[7];
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@TableName";
            parameter.DbType = DbType.String;
            parameter.Value = tableName;//订单表
            parameters[0] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@ReFieldsStr";
            parameter.DbType = DbType.String;
            if (string.IsNullOrWhiteSpace(fileds))
            {
                parameter.Value = "*";
            }
            else
            {
                parameter.Value = fileds;
            }
            parameters[1] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@OrderString";
            parameter.DbType = DbType.String;
            parameter.Value = orderStr;// "pi_id";
            parameters[2] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@WhereString";
            parameter.DbType = DbType.String;
            parameter.Value = sqlWhere;
            parameters[3] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@PageSize";
            parameter.DbType = DbType.Int64;
            parameter.Value = pageSize;
            parameters[4] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@PageIndex";
            parameter.DbType = DbType.Int64;
            parameter.Value = pageIndex;
            parameters[5] = parameter;
            parameter = new SqlParameter();
            parameter.ParameterName = "@TotalRecord";
            parameter.DbType = DbType.Int64;
            parameter.Value = 1;
            parameters[6] = parameter;
            return db.PageList(ref pageCount, parameters);
        }
        #endregion
    }
}
